// backend/server.js

const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors');

const app = express();
const port = 3008;

// 配置跨域
app.use(cors());

// 解析请求体
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// 连接MySQL数据库
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',       // 根据实际情况修改
  password: 'a77803901a',       // 根据实际情况修改
  database: 'library_system',
});

db.connect((err) => {
  if (err) {
    console.error('数据库连接失败:', err);
    process.exit(1);
  }
  console.log('MySQL connected...');
});

// 用户登录接口
app.post('/login', (req, res) => {
  const { username, password } = req.body;
  const query = `SELECT * FROM users WHERE username = ? AND password = ?`;
  db.query(query, [username, password], (err, results) => {
    if (err) {
      console.error('登录错误:', err);
      return res.status(500).send('服务器错误');
    }
    if (results.length > 0) {
      // 记录日志
      const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
      db.query(logQuery, [`用户 ${username} 登录`, username], (logErr) => {
        if (logErr) console.error('日志记录失败:', logErr);
      });
      res.json({ success: true, user: results[0] });
    } else {
      res.status(401).json({ success: false, message: '用户名或密码错误' });
    }
  });
});

// 添加新用户
app.post('/users', (req, res) => {
  const { username, password, role, admin_username } = req.body;

  console.log('[DEBUG] 收到的添加用户请求体:', req.body);

  // ① 检查必填字段
  if (!username || !password || !role) {
    console.log('[DEBUG] 缺少必填字段，返回400');
    return res.status(400).json({ success: false, message: '缺少必填字段' });
  }

  // ② 检查用户名是否已存在
  const checkQuery = `SELECT * FROM users WHERE username = ?`;
  db.query(checkQuery, [username], (checkErr, checkResults) => {
    if (checkErr) {
      return res.status(500).json({ success: false, message: '服务器错误', error: checkErr.message });
    }
    console.log('[DEBUG] 查询到的用户:', checkResults); // 看看数据库里是否有同名

    if (checkResults.length > 0) {
      // ③ 如果用户名已存在，直接返回 400
      return res.status(400).json({ success: false, message: '用户名已存在' });
    }

    // ④ 插入新用户
    const insertQuery = `INSERT INTO users (username, password, role) VALUES (?, ?, ?)`;
    db.query(insertQuery, [username, password, role], (insertErr, insertResults) => {
      if (insertErr) {
        return res.status(500).json({ success: false, message: '服务器错误', error: insertErr.message });
      }
      console.log('[DEBUG] 插入新用户成功:', insertResults);

      // 记录日志
      const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
      db.query(logQuery, [`添加用户: ${username}`, admin_username || '未知用户'], (logErr) => {
        if (logErr) console.error('日志记录失败:', logErr);
      });

      res.status(201).json({ success: true, message: '用户添加成功' });
    });
  });
});


// 获取所有用户（仅管理员可用）
app.get('/users', (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;

  const countQuery = `SELECT COUNT(*) as total FROM users`;
  const dataQuery = `SELECT id, username, role, created_at FROM users LIMIT ? OFFSET ?`;

  db.query(countQuery, (countErr, countResults) => {
    if (countErr) {
      console.error('获取用户总数失败:', countErr);
      return res.status(500).send('服务器错误');
    }
    const total = countResults[0].total;

    db.query(dataQuery, [parseInt(limit), parseInt(offset)], (dataErr, dataResults) => {
      if (dataErr) {
        console.error('获取用户失败:', dataErr);
        return res.status(500).send('服务器错误');
      }
      res.json({ users: dataResults, total });
    });
  });
});

// 更新用户信息（仅管理员可用）
// 后端 server.js 中的更新用户逻辑示例
app.put('/users/:id', (req, res) => {
  const { username, password, role, admin_username } = req.body;
  const userId = req.params.id;

  // 如果后端希望在这里也检查必填字段 (username/role等)
  if (!username || !role) {
    return res.status(400).json({ success: false, message: '缺少必填字段' });
  }

  // 根据是否存在 password 字段，动态更新
  // password === undefined 表示前端没有传 password => 不更新密码
  // password === '' 也要看您是否要允许写成空字符串
  if (typeof password === 'undefined') {
    // 不更新密码
    const query = `UPDATE users SET username = ?, role = ? WHERE id = ?`;
    const values = [username, role, userId];

    db.query(query, values, (err, results) => {
      if (err) {
        console.error('更新用户失败:', err);
        return res
          .status(500)
          .json({ success: false, message: '服务器错误', error: err.message });
      }
      // 记录日志
      const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
      db.query(logQuery, [`更新用户 ID: ${userId}`, admin_username || '未知用户'], (logErr) => {
        if (logErr) console.error('日志记录失败:', logErr);
      });
      res.json({ success: true, message: '用户更新成功' });
    });
  } else {
    // 前端传了 password => 可能是修改密码
    // 如果数据库 password NOT NULL，但前端确实想置空 => 需自行决定是否允许
    // 常规场景：password !== null / undefined
    const query = `UPDATE users SET username = ?, role = ?, password = ? WHERE id = ?`;
    const values = [username, role, password, userId];

    db.query(query, values, (err, results) => {
      if (err) {
        console.error('更新用户失败:', err);
        return res
          .status(500)
          .json({ success: false, message: '服务器错误', error: err.message });
      }
      // 记录日志
      const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
      db.query(logQuery, [`更新用户 ID: ${userId}`, admin_username || '未知用户'], (logErr) => {
        if (logErr) console.error('日志记录失败:', logErr);
      });
      res.json({ success: true, message: '用户更新成功' });
    });
  }
});


// 删除用户（仅管理员可用）
app.delete('/users/:id', (req, res) => {
  const { id } = req.params;
  const query = `DELETE FROM users WHERE id = ?`;
  db.query(query, [id], (err, results) => {
    if (err) {
      console.error('删除用户失败:', err);
      return res.status(500).send('服务器错误');
    }
    // 记录日志
    const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
    db.query(logQuery, [`删除用户 ID: ${id}`, req.body.username || '未知用户'], (logErr) => {
      if (logErr) console.error('日志记录失败:', logErr);
    });
    res.json({ success: true, message: '用户删除成功' });
  });
});

// 获取所有图书
app.get('/books', (req, res) => {
  const { search, page = 1, limit = 10 } = req.query;
  let query = `SELECT * FROM books`;
  let countQuery = `SELECT COUNT(*) as total FROM books`;

  if (search) {
    query += ` WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ? OR category LIKE ?`;
    countQuery += ` WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ? OR category LIKE ?`;
  }

  if (search) {
    const searchTerm = `%${search}%`;
    db.query(countQuery, [searchTerm, searchTerm, searchTerm, searchTerm], (countErr, countResults) => {
      if (countErr) {
        console.error('获取图书总数失败:', countErr);
        return res.status(500).send('服务器错误');
      }
      const total = countResults[0].total;
      query += ` LIMIT ?, ?`;
      const offset = (page - 1) * limit;
      db.query(query, [searchTerm, searchTerm, searchTerm, searchTerm, offset, parseInt(limit)], (err, results) => {
        if (err) {
          console.error('获取图书失败:', err);
          return res.status(500).send('服务器错误');
        }
        res.json({ books: results, total });
      });
    });
  } else {
    const offset = (page - 1) * limit;
    db.query(countQuery, (countErr, countResults) => {
      if (countErr) {
        console.error('获取图书总数失败:', countErr);
        return res.status(500).send('服务器错误');
      }
      const total = countResults[0].total;
      query += ` LIMIT ?, ?`;
      db.query(query, [offset, parseInt(limit)], (err, results) => {
        if (err) {
          console.error('获取图书失败:', err);
          return res.status(500).send('服务器错误');
        }
        res.json({ books: results, total });
      });
    });
  }
});

// 添加新图书
app.post('/books', (req, res) => {
  const { title, author, isbn, category } = req.body;
  const query = `INSERT INTO books (title, author, isbn, category) VALUES (?, ?, ?, ?)`;
  db.query(query, [title, author, isbn, category], (err, results) => {
    if (err) {
      console.error('添加图书失败:', err);
      return res.status(500).send('服务器错误');
    }
    // 记录日志
    const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
    db.query(logQuery, [`添加图书: ${title}`, req.body.username || '未知用户'], (logErr) => {
      if (logErr) console.error('日志记录失败:', logErr);
    });
    res.status(201).json({ success: true, message: '图书添加成功' });
  });
});

// 更新图书信息
app.put('/books/:id', (req, res) => {
  const { title, author, isbn, category, status } = req.body;
  const { id } = req.params;
  const query = `UPDATE books SET title = ?, author = ?, isbn = ?, category = ?, status = ? WHERE id = ?`;
  db.query(query, [title, author, isbn, category, status, id], (err, results) => {
    if (err) {
      console.error('更新图书失败:', err);
      return res.status(500).send('服务器错误');
    }
    // 记录日志
    const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
    db.query(logQuery, [`更新图书 ID: ${id}`, req.body.username || '未知用户'], (logErr) => {
      if (logErr) console.error('日志记录失败:', logErr);
    });
    res.json({ success: true, message: '图书更新成功' });
  });
});

// 删除图书
app.delete('/books/:id', (req, res) => {
  const { id } = req.params;
  const query = `DELETE FROM books WHERE id = ?`;
  db.query(query, [id], (err, results) => {
    if (err) {
      console.error('删除图书失败:', err);
      return res.status(500).send('服务器错误');
    }
    // 记录日志
    const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
    db.query(logQuery, [`删除图书 ID: ${id}`, req.body.username || '未知用户'], (logErr) => {
      if (logErr) console.error('日志记录失败:', logErr);
    });
    res.json({ success: true, message: '图书删除成功' });
  });
});

// 借阅图书
app.post('/borrow', (req, res) => {
  const { user_id, book_id, username } = req.body;

  // 检查图书是否可借
  const checkQuery = `SELECT status FROM books WHERE id = ?`;
  db.query(checkQuery, [book_id], (err, results) => {
    if (err) {
      console.error('检查图书状态失败:', err);
      return res.status(500).send('服务器错误');
    }
    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '图书不存在' });
    }
    if (results[0].status !== 'available') {
      return res.status(400).json({ success: false, message: '图书当前不可借' });
    }

    // 更新图书状态为借出
    const updateBookQuery = `UPDATE books SET status = 'borrowed' WHERE id = ?`;
    db.query(updateBookQuery, [book_id], (updateErr) => {
      if (updateErr) {
        console.error('更新图书状态失败:', updateErr);
        return res.status(500).send('服务器错误');
      }

      // 插入借阅记录，borrow_date 会自动设置为当前时间
      const borrowQuery = `INSERT INTO borrow_records (user_id, book_id) VALUES (?, ?)`;
      db.query(borrowQuery, [user_id, book_id], (borrowErr) => {
        if (borrowErr) {
          console.error('插入借阅记录失败:', borrowErr);
          return res.status(500).send('服务器错误');
        }

        // 记录日志
        const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
        db.query(logQuery, [`用户 ID: ${user_id} 借阅图书 ID: ${book_id}`, username || '未知用户'], (logErr) => {
          if (logErr) console.error('日志记录失败:', logErr);
        });

        res.json({ success: true, message: '图书借阅成功' });
      });
    });
  });
});

// 归还图书
app.post('/return', (req, res) => {
  const { borrow_id, username } = req.body;

  // 获取借阅记录
  const getBorrowQuery = `SELECT * FROM borrow_records WHERE id = ? AND status = 'borrowed'`;
  db.query(getBorrowQuery, [borrow_id], (err, results) => {
    if (err) {
      console.error('获取借阅记录失败:', err);
      return res.status(500).send('服务器错误');
    }
    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '借阅记录不存在或已归还' });
    }
    const { user_id, book_id } = results[0];

    // 更新借阅记录为已归还，return_date 会自动设置为当前日期
    const updateBorrowQuery = `UPDATE borrow_records SET status = 'returned', return_date = CURRENT_DATE WHERE id = ?`;
    db.query(updateBorrowQuery, [borrow_id], (updateErr) => {
      if (updateErr) {
        console.error('更新借阅记录失败:', updateErr);
        return res.status(500).send('服务器错误');
      }

      // 更新图书状态为可用
      const updateBookQuery = `UPDATE books SET status = 'available' WHERE id = ?`;
      db.query(updateBookQuery, [book_id], (bookErr) => {
        if (bookErr) {
          console.error('更新图书状态失败:', bookErr);
          return res.status(500).send('服务器错误');
        }

        // 记录日志
        const logQuery = `INSERT INTO logs (action, username) VALUES (?, ?)`;
        db.query(logQuery, [`用户 ID: ${user_id} 归还图书 ID: ${book_id}`, username || '未知用户'], (logErr) => {
          if (logErr) console.error('日志记录失败:', logErr);
        });

        res.json({ success: true, message: '图书归还成功' });
      });
    });
  });
});

// 获取所有借阅记录
app.get('/borrow_records', (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;

  const countQuery = 'SELECT COUNT(*) AS total FROM borrow_records';
  const dataQuery = `
    SELECT br.id, u.username, b.title, br.borrow_date, br.return_date, br.status
    FROM borrow_records br
    JOIN users u ON br.user_id = u.id
    JOIN books b ON br.book_id = b.id
    ORDER BY br.borrow_date DESC
    LIMIT ? OFFSET ?
  `;

  db.query(countQuery, (countErr, countResults) => {
    if (countErr) {
      console.error('获取借阅记录总数失败:', countErr);
      return res.status(500).send('服务器错误');
    }
    const total = countResults[0].total;

    db.query(dataQuery, [parseInt(limit), parseInt(offset)], (dataErr, dataResults) => {
      if (dataErr) {
        console.error('获取借阅记录失败:', dataErr);
        return res.status(500).send('服务器错误');
      }
      // 返回 { borrow_records: [...], total: number }
      res.json({ borrow_records: dataResults, total });
    });
  });
});

// 获取所有日志
app.get('/logs', (req, res) => {
  const { page = 1, limit = 10 } = req.query;
  const offset = (page - 1) * limit;

  const countQuery = 'SELECT COUNT(*) AS total FROM logs';
  const dataQuery = `
    SELECT * FROM logs 
    ORDER BY created_at DESC 
    LIMIT ? OFFSET ?
  `;
  db.query(countQuery, (countErr, countResults) => {
    if (countErr) {
      console.error('获取日志总数失败:', countErr);
      return res.status(500).send('服务器错误');
    }
    const total = countResults[0].total;

    db.query(dataQuery, [parseInt(limit), parseInt(offset)], (dataErr, dataResults) => {
      if (dataErr) {
        console.error('获取日志失败:', dataErr);
        return res.status(500).send('服务器错误');
      }
      // 返回 { logs: [...], total: number }
      res.json({ logs: dataResults, total });
    });
  });
});


// 启动服务器
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});
